#include <qsqldatabase.h>
#include <qmessagebox.h>
#include <qsqlquery.h>
#include <qsortfilterproxymodel.h>
#include <qfile.h>
#include <QDate>
#include <qsqlerror.h>

#include "databasemodel.h"
#include "timespandialog.h"

#include "ui_databasedialog.h"
#include "databasedialog.h"

const QString DatabaseDialog::TABLE_NAME = "TimeToGoHome";

DatabaseDialog::DatabaseDialog(QWidget *parent) :
    QDialog(parent, Qt::WindowMinMaxButtonsHint | Qt::WindowCloseButtonHint),
    ui(new Ui::DatabaseDialog),
    m_pModel(nullptr), m_hasError(false)
{
    ui->setupUi(this);

    const QString style("background-color: %1;");
    ui->lblOk->setStyleSheet(style.arg(DatabaseModel::COLOR_OK.name()));
    ui->lblNoTw->setStyleSheet(style.arg(DatabaseModel::COLOR_NO_TW.name()));
    ui->lblTooMuchTw->setStyleSheet(style.arg(DatabaseModel::COLOR_TOOMUCH_TW.name()));
    ui->lblTooLessTw->setStyleSheet(style.arg(DatabaseModel::COLOR_TOOLESS_TW.name()));

    // Datenbank initialisieren
    if (!initDb())
    {
        m_hasError = true;
        return;
    }

    // Tisoware-Daten abfragen
    initTisowareDb();
    //loadTisowareData();
    ui->lblSumTw->setText(secondsToString(m_pModel->sumTisoware()));

    ui->tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::ResizeToContents);
    //adjustSize();

    // Eintrag einfügen
    if (m_pModel)
    {
        if (!m_pModel->addToday())
            QMessageBox::critical(this, QString(), "Failed to add row!");
    }

    connect(m_pModel, SIGNAL(dataChanged(QModelIndex,QModelIndex)), SLOT(showTotalWorkTime()));
    showTotalWorkTime();
}

DatabaseDialog::~DatabaseDialog()
{
    delete ui;
}

void DatabaseDialog::updateToday(const QTime &i_begin, const QTime &i_break)
{
    // Eintrag aktualisieren
    if (m_pModel)
    {
        if (!m_pModel->updateToday(i_begin, i_break))
            QMessageBox::critical(this, QString(), "Failed to update row!");
    }
}

void DatabaseDialog::updateTodaysEnd(int i_msecsToAdd)
{
    if (m_pModel)
        m_pModel->updateTodaysEnd(i_msecsToAdd);
}

void DatabaseDialog::loadTisowareData()
{
    if (!QSqlDatabase::contains("tisoware"))
        return;

    // Bereich abfragen
    TimeSpanDialog dlg(m_pModel->minimumDate(),
                       m_pModel->maximumDate(),
                       this);
    if (dlg.exec() != QDialog::Accepted)
        return;

    QString errorText;

    const int days = m_pModel->updateTisowareData(errorText,
                                                  dlg.dateFrom(),
                                                  dlg.dateTo());

    if (days > -1)
    {
        QMessageBox::information(this, QString(),
                                 QString("%1 Tage aktualisiert.").arg(days));
    }
    else
    {
        QMessageBox::critical(this, QString(), errorText);
    }

    ui->lblSumTw->setText(secondsToString(m_pModel->sumTisoware()));
}

void DatabaseDialog::showTotalWorkTime()
{
    ui->lblSumWork->setText(secondsToString(m_pModel->sumWork()));
}

bool DatabaseDialog::initDb()
{    
    if (!QSqlDatabase::drivers().contains("QSQLITE"))
    {
        QMessageBox::critical(this, "Unable to load database",
                              "This application needs the SQLITE driver!");
        return false;
    }

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("db.sqlite");

    if (!db.open())
    {
        QMessageBox::critical(this, QString(), "Failed to setup database connection!");
        return false;
    }

    //m_pModel->setTable(TABLE_NAME);

    if (/*!m_pModel->select()*/!db.tables().contains(TABLE_NAME))
    {
        qDebug("Create DB");

        // Datenbank erst anlegen
        if (!createDb())
            return false;
    }

    m_pModel = new DatabaseModel(this);

    if (!upgradeDb())
        return false;

    m_pModel->setHeaderData(DatabaseModel::ID, Qt::Horizontal, "ID");
    m_pModel->setHeaderData(DatabaseModel::Date, Qt::Horizontal, "Datum");
    m_pModel->setHeaderData(DatabaseModel::Begin, Qt::Horizontal, "Anfang");
    m_pModel->setHeaderData(DatabaseModel::End, Qt::Horizontal, "Ende");
    m_pModel->setHeaderData(DatabaseModel::Break, Qt::Horizontal, "Pause");
    m_pModel->setHeaderData(DatabaseModel::Comment, Qt::Horizontal, "Kommentar");
    m_pModel->setHeaderData(DatabaseModel::TwTime, Qt::Horizontal, "Rückgemeldet");
    m_pModel->setHeaderData(DatabaseModel::Duration, Qt::Horizontal, "Dauer");
    m_pModel->setHeaderData(DatabaseModel::Status, Qt::Horizontal, "Status");
    m_pModel->setHeaderData(DatabaseModel::Weekday, Qt::Horizontal, "Wochentag");
    QSortFilterProxyModel* pSortProxy = new QSortFilterProxyModel(this);
    pSortProxy->setSourceModel(m_pModel);
    ui->tableView->setModel(pSortProxy);
    ui->tableView->horizontalHeader()->moveSection(DatabaseModel::Comment, DatabaseModel::Duration);
    ui->tableView->horizontalHeader()->moveSection(DatabaseModel::TwTime, DatabaseModel::Comment);
    ui->tableView->horizontalHeader()->moveSection(DatabaseModel::Weekday, DatabaseModel::Date);
    ui->tableView->hideColumn(DatabaseModel::ID);
    ui->tableView->sortByColumn(DatabaseModel::Date, Qt::DescendingOrder);

    return true;
}

bool DatabaseDialog::createDb()
{
    QSqlQuery q;
    const bool success = q.exec(QString("CREATE TABLE [%1] (\n"
                  "[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \n"
                  "[date] DATE NOT NULL CONSTRAINT [unique_date] UNIQUE, \n"
                  "[begin] TIME NOT NULL DEFAULT 0, \n"
                  "[end] TIME NOT NULL DEFAULT 0, \n"
                  "[break] TIME NOT NULL DEFAULT 0);").arg(TABLE_NAME));

    if (!success)
        QMessageBox::critical(this, QString(), "Failed to create database!");
    /*else
        m_pModel->setTable(TABLE_NAME);*/

    return success;
}

bool DatabaseDialog::upgradeDb()
{
    // Version holen
    QSqlQuery q;
    q.setForwardOnly(true);
    q.prepare(QStringLiteral("SELECT version FROM Version ORDER BY version DESC LIMIT 1"));
    int version = 0;

    if (q.exec() && q.next())
    {
        version = q.value("version").toInt();
    }
    q.finish();

    qDebug("Database version is [%i]", version);

    if (version < DB_VERSION)
    {        
        // Erst alte DB sichern
        const QString dbFile(QSqlDatabase::database().databaseName());
        const QString backupFile(dbFile + ".bak");

        if (!QFile::copy(dbFile, backupFile))
        {
            QMessageBox::critical(this, QString(),
                                  QString("Failed to backup the current database file to [%1]!\n"
                                          "File already exists?").
                                  arg(backupFile));
            return false;
        }

        int errorVersion = 0;

        switch (version)
        {
        case 0:
            if (!upgradeDb0to1())
            {
                errorVersion = 1;
                break;
            }
        case 1:
            if (!upgradeDb1to2())
            {
                errorVersion = 2;
                break;
            }
        case 2:
            if (!upgradeDb2to3())
            {
                errorVersion =3;
                break;
            }
        }

        if (errorVersion > 0)
        {
            QMessageBox::critical(this, QString(),
                                  QString("Failed to upgrade from version [%1] to [%2]!\n"
                                          "All previous steps were successful.\n"
                                          "The original database was backed up to [%3].").
                                  arg(errorVersion - 1).arg(errorVersion).arg(backupFile));

            return false;
        }

        QMessageBox::information(this, QString(),
                                 QString("The database was upgraded from version [%1] to [%2]!\n"
                                         "The original database was backed up to [%3].").
                                 arg(version).arg(DB_VERSION).arg(backupFile));
    }

    m_pModel->setTable(TABLE_NAME);
    return m_pModel->select();
}

void DatabaseDialog::initTisowareDb()
{
    if (!QSqlDatabase::drivers().contains("QODBC"))
    {
        QMessageBox::critical(this, "Unable to load tisoware database",
                              "This application needs the QODBC driver!");
        ui->btnLoadTisowareData->setDisabled(true);
        return;
    }

    QSqlDatabase dbTiso = QSqlDatabase::addDatabase("QODBC", "tisoware");
    dbTiso.setDatabaseName("Driver={SQL Server Native Client 10.0};"
                           "Server=dedb-db02;Database=tisoware;"
                           "Uid=tisoware;Pwd=tisoware;");
}

bool DatabaseDialog::upgradeDb0to1()
{
    QSqlQuery q;
    const QStringList statements = QStringList()
            << QString("ALTER TABLE [%1] ADD COLUMN [comment] TEXT NOT NULL DEFAULT '';").arg(TABLE_NAME)
            << "CREATE TABLE [Version] (\n"
               "[version] INTEGER NOT NULL PRIMARY KEY,\n"
               "[name] TEXT NOT NULL UNIQUE);"
            << "INSERT INTO [Version] (version,name)\n"
               "VALUES (1,'Comments');";

    foreach (const QString& sql, statements)
    {
        if (!q.exec(sql))
            return false;
        q.finish();
    }

    return true;
}

bool DatabaseDialog::upgradeDb1to2()
{
    QSqlQuery q;
    const QStringList statements = QStringList()
            << QString("ALTER TABLE [%1] ADD COLUMN [tw_time] TIME NOT NULL DEFAULT 0;").arg(TABLE_NAME)
            << "INSERT INTO [Version] (version,name)\n"
               "VALUES (2,'Tisoware');";

    foreach (const QString& sql, statements)
    {
        if (!q.exec(sql))
            return false;
        q.finish();
    }

    return true;
}

bool DatabaseDialog::upgradeDb2to3()
{
    QSqlQuery q;
    const QStringList statements = QStringList()
            << "CREATE TABLE [tempTable] (\n"
               "[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \n"
               "[date] DATE NOT NULL CONSTRAINT [unique_date] UNIQUE, \n"
               "[begin] TIME NOT NULL DEFAULT (time(0,'unixepoch')), \n"
               "[end] TIME NOT NULL DEFAULT (time(0,'unixepoch')), \n"
               "[break] TIME NOT NULL DEFAULT (time(0,'unixepoch')), \n"
               "[comment] TEXT NOT NULL DEFAULT '', \n"
               "[tw_time] TIME NOT NULL DEFAULT (time(0,'unixepoch')));"
            << QString("INSERT INTO [tempTable] SELECT * FROM %1;").arg(TABLE_NAME)
            << "UPDATE [tempTable] SET tw_time = time(0,'unixepoch') "
               "WHERE (strftime('%s', tw_time) - strftime('%s', '00:00:00')) < 0;"
            << QString("DROP TABLE [%1];").arg(TABLE_NAME)
            << QString("ALTER TABLE [tempTable] RENAME TO [%1];").arg(TABLE_NAME)
            << "INSERT INTO [Version] (version,name)\n"
               "VALUES (3,'TIME columns default value');";

    foreach (const QString& sql, statements)
    {
        if (!q.exec(sql))
        {
            qDebug("SQL: %s", qPrintable(sql));
            qDebug("Error: %s", qPrintable(q.lastError().text()));
            return false;
        }
        q.finish();
    }

    return true;
}

QString DatabaseDialog::secondsToString(int i_seconds)
{
    const int days = i_seconds / 60 / 60 / 24;
    const int hours = (i_seconds / 60 / 60) % 24;
    const int minutes = (i_seconds / 60) % 60;
    const int secs = i_seconds % 60;
    return QString("%1 Tag%2 %3:%4:%5").arg(days).
            arg(days == 1 ? QString() : QString("e")).
            arg(hours, 2, 10, QChar('0')).arg(minutes, 2, 10, QChar('0')).
            arg(secs, 2, 10, QChar('0'));
}
